--每天数据量5000
CREATE TABLE jms_dm.dm_end_network_taking_notout_over22d_detail_dt (
   virt_code varchar(30) NULL COMMENT "虚拟代理区code",
   agent_code varchar(30) NULL COMMENT "代理区code",
   fran_code varchar(30) NULL COMMENT "加盟商code",
   ordersource_name varchar(30) NULL COMMENT "订单来源",
   waybill_no varchar(30) NULL COMMENT "运单号",
   taking_network_code varchar(30) NULL COMMENT "最早揽收网点code",
   taking_network_name varchar(120) NULL COMMENT "最早揽收网点name",
   taking_scan_time datetime NULL COMMENT "最早揽收时间",
   in_scan_time datetime NULL COMMENT "最早入库时间",
   in_network_code varchar(30) NULL COMMENT "最早入库网点",
   in_network_name varchar(120) NULL COMMENT "最早入库网点",
   store_code varchar(60) NULL COMMENT "最早入库门店",
   deliver_scan_time datetime NULL COMMENT "最早派件时间",
   out_scan_time datetime NULL COMMENT "出库扫描时间",
   business_id varchar(30) NULL COMMENT "品牌id",
   business_name varchar(120) NULL COMMENT "品牌名称",
   store_name varchar(120) NULL COMMENT "门店名称",
   store_address varchar(120) NULL COMMENT "门店地址",
   build_type varchar(30) NULL COMMENT "建设类型：1自建2合作 null",
   source_type varchar(30) NULL COMMENT "末端类型：机柜、驿站",
   agent_name varchar(60) NULL COMMENT "代理区name",
   fran_name varchar(120) NULL COMMENT "加盟商name",
   out_taking_diffday int(11) NULL COMMENT "nvl(出库日期-揽收日期,99),揽收和出库天间隔",
   date_time date NULL COMMENT "查询时间",
   virt_name varchar(50) NULL COMMENT "虚拟代理区name"
) ENGINE=OLAP 
DUPLICATE KEY(virt_code, agent_code, fran_code, ordersource_name)
COMMENT "末端_揽收超22日未出库_明细"
PARTITION BY RANGE (date_time) ( 
   START ("2023-11-01") END ("2023-12-30") EVERY (INTERVAL 1 day))
DISTRIBUTED BY HASH(in_network_code) BUCKETS 2
PROPERTIES (
"replication_num" = "3",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-186",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "2",
"in_memory" = "false",
"storage_format" = "V2",
"enable_persistent_index" = "false"
);